--顺丰仓库基本档案dml
INSERT INTO WMS_WAREHOUSE (ID,WAREHOUSE_CODE,WAREHOUSE_NAME)
	SELECT WMS_SEQ_COMMON.nextval,'SF','顺丰仓' FROM DUAL;

-- 调拨单添加列，订单推送到顺丰状态，0未推送，1已经推送
ALTER TABLE WMS_TRANSFER ADD ORDER_PUSH_STATUS VARCHAR2(2) NULL;
COMMENT ON COLUMN WMS_TRANSFER.ORDER_PUSH_STATUS IS '订单推送到顺丰状态，0未推送，1已推送';

-- 调拨单添加列，顺丰确认收货状态，0未收货，1已收货
ALTER TABLE WMS_TRANSFER ADD ORDER_CONFIRM_STATUS VARCHAR2(2) NULL;
COMMENT ON COLUMN "WMS_TRANSFER"."ORDER_CONFIRM_STATUS" IS '顺丰确认收货状态，0未收货，1已收货';

-- 调拨商品添加列，顺丰实收数量
ALTER TABLE WMS_TRANSFER_GOODS ADD QTY NUMBER NULL;
COMMENT ON COLUMN "WMS_TRANSFER_GOODS"."QTY" IS '顺丰实收数量';

-- SKU映射表添加列，商品推送到顺丰状态，0未推送，1已经推送
ALTER TABLE WMS_SKU_MAP ADD SKU_PUSH_STATUS INTEGER DEFAULT 0;
COMMENT ON COLUMN "WMS_SKU_MAP"."SKU_PUSH_STATUS" IS 'SKU推送到顺丰状态，0未推送，1已经推送';

-- 销售订单添加列，订单推送到顺丰状态，0和null未推送，1已经推送
ALTER TABLE WMS_SALES_ORDER ADD ORDER_PUSH_STATUS VARCHAR2(2) NULL;
COMMENT ON COLUMN WMS_SALES_ORDER.ORDER_PUSH_STATUS IS '订单推送到顺丰状态，0和null未推送，1已经推送';

-- 创建系统配置表
CREATE TABLE "GIONEE_WMS"."WMS_SYSTEM_CONFIG" (
"id" NUMBER NOT NULL ,
"key" VARCHAR2(256) NULL ,
"value" VARCHAR2(1024) NULL ,
"comment" VARCHAR2(512) NULL ,
"status" NUMBER NOT NULL ,
PRIMARY KEY ("id")
);

COMMENT ON COLUMN "GIONEE_WMS"."WMS_SYSTEM_CONFIG"."status" IS '系统配置表';
INSERT INTO WMS_SYSTEM_CONFIG("id", "key", "value", "comment", "status") VALUES(1, 'ORDER_AUTO_PUSH_SF', '0', '是否自动推送到顺丰', 0);

-- 创建系统配置表序列
CREATE SEQUENCE WMS_SYSTEM_CONFIG_ID_SEQ;


-- 顺丰订单和WMS订单映射表
CREATE TABLE "WMS_SALES_ORDER_MAP" (
"ID" VARCHAR2(32) NOT NULL ,
"ORDER_CODE" VARCHAR2(32) NULL ,
"SF_ERP_ORDER" VARCHAR2(32) NULL ,
"ACTUAL_SHIP_DATE_TIME" DATE NULL ,
"CARRIER" VARCHAR2(64) NULL ,
"CARRIER_SERVICE" VARCHAR2(64) NULL ,
PRIMARY KEY ("ID")
);

COMMENT ON TABLE "WMS_SALES_ORDER_MAP" IS '顺丰订单和WMS订单映射表';
COMMENT ON COLUMN "WMS_SALES_ORDER_MAP"."ID" IS '主键ID';
COMMENT ON COLUMN "WMS_SALES_ORDER_MAP"."ORDER_CODE" IS '销售订单号';
COMMENT ON COLUMN "WMS_SALES_ORDER_MAP"."SF_ERP_ORDER" IS '顺丰ERP订单号';
COMMENT ON COLUMN "WMS_SALES_ORDER_MAP"."ACTUAL_SHIP_DATE_TIME" IS '发货时间';
COMMENT ON COLUMN "WMS_SALES_ORDER_MAP"."CARRIER" IS '承运商名';
COMMENT ON COLUMN "WMS_SALES_ORDER_MAP"."CARRIER_SERVICE" IS '承运商服务类型 /SF:时效类型';

-- 顺丰出库单串号对应表
CREATE TABLE "WMS_SALES_ORDER_IMEI" (
"ID" VARCHAR2(32) NOT NULL ,
"ORDER_CODE" VARCHAR2(32) NULL ,
"SF_ERP_ORDER" VARCHAR2(32) NULL ,
"IMEI" VARCHAR2(32) NULL ,
PRIMARY KEY ("ID")
);

COMMENT ON TABLE "WMS_SALES_ORDER_IMEI" IS '顺丰出库单串号对应表';
COMMENT ON COLUMN "WMS_SALES_ORDER_IMEI"."ORDER_CODE" IS '销售订单号';
COMMENT ON COLUMN "WMS_SALES_ORDER_IMEI"."SF_ERP_ORDER" IS '顺丰ERP订单号';
COMMENT ON COLUMN "WMS_SALES_ORDER_IMEI"."IMEI" IS '串号';

